﻿Imports System.Data.OleDb
Imports DTO

Public Class SachDAO

    Public Sub NhapSach(ByVal sach As SachDTO, ByVal cts As ChiTietSachDTO)
        '   Dim kq As Boolean = True

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "Insert into Sach (TenSach, idTheLoai, Gia, DaXoa, NgayNhap, idTacGia,SoLuong) values (?, ?, ?, ?, ?, ?, ?)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@TenSach", OleDbType.WChar)
        cmd.Parameters.Add("@idTheLoai", OleDbType.Integer)
        cmd.Parameters.Add("@Gia", OleDbType.WChar)
        cmd.Parameters.Add("@DaXoa", OleDbType.Integer)
        cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
        cmd.Parameters.Add("@idTacGia", OleDbType.WChar)
        cmd.Parameters.Add("@SoLuong", OleDbType.Integer)

        cmd.Parameters("@TenSach").Value = sach.LayTenSach
        cmd.Parameters("@idTheLoai").Value = sach.LayIDTheLoai
        cmd.Parameters("@Gia").Value = sach.LayGiaSach
        cmd.Parameters("@DaXoa").Value = 0
        cmd.Parameters("@NgayNhap").Value = sach.LayNgayNhap
        cmd.Parameters("@idTacGia").Value = sach.layTacGia
        cmd.Parameters("@SoLuong").Value = sach.LaySoLuong

        cmd.ExecuteNonQuery()
        sql = "Select @@IDENTITY"
        cmd = New OleDbCommand(sql, cn)
        sach.LayIdSach = cmd.ExecuteScalar()
        cts.LayIDSach = sach.LayIdSach
        Dim chitiet As New ChiTietSachDAO
        chitiet.ThemChiTietSach(cts)
        '   ChiTietSachDAO.ThemChiTietSach(cts)
        'B5: Dong ket noi CSDL

        cn.Close()
        '     Return kq
    End Sub

    Public Sub NhapSachTonTai(ByVal sach As SachDTO, ByVal cts As ChiTietSachDTO)
        '   Dim kq As Boolean = True

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "Update Sach  set  NgayNhap= ? ,SoLuong = ? where idSach = ? "
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
        cmd.Parameters.Add("@SoLuong", OleDbType.Integer)

        cmd.Parameters("@NgayNhap").Value = sach.LayNgayNhap
        cmd.Parameters("@SoLuong").Value = sach.LaySoLuong
        cmd.ExecuteNonQuery()

        'B5: Dong ket noi CSDL
        cn.Close()
        '     Return kq
    End Sub

    Public Sub XoaSach(ByVal book As SachDTO)
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "update Sach set DaXoa=1 where idSach= ? "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)
        cmd.Parameters("@idSach").Value = book.LayIdSach
        cmd.ExecuteNonQuery()
        cn.Close()

    End Sub

    Public Sub CapNhatSach(ByVal book As SachDTO)
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "Update Sach set TenSach = ?, idTacGia = ? ,idTheLoai = ?, Gia = ? , NgayNhap = ?, SoLuong = ? where idSach = ?"
        Dim cmd As New OleDbCommand(sql, cn)


        cmd.Parameters.Add("@TenSach", OleDbType.WChar)
        cmd.Parameters.Add("@idTacGia", OleDbType.WChar)
        cmd.Parameters.Add("@idTheLoai", OleDbType.WChar)
        cmd.Parameters.Add("@Gia", OleDbType.WChar)
        cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
        cmd.Parameters.Add("@SoLuong", OleDbType.WChar)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)

        cmd.Parameters("@TenSach").Value = book.LayTenSach
        cmd.Parameters("@idTacGia").Value = book.layTacGia
        cmd.Parameters("@idTheLoai").Value = book.LayIDTheLoai
        cmd.Parameters("@Gia").Value = book.LayGiaSach
        cmd.Parameters("@NgayNhap").Value = book.LayNgayNhap
        cmd.Parameters("@SoLuong").Value = book.LaySoLuong
        cmd.Parameters("@idSach").Value = book.LayIdSach
        cmd.ExecuteNonQuery()

        cn.Close()
    End Sub

    Public Function LayTenSach(ByVal s As SachDTO) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select * from Sach where idSach = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)
        cmd.Parameters("@idSach").Value = s.LayIdSach()
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Function LayDanhSachSach() As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String

        sql = "select TenSach, TenTacGia, NgayNhap, Gia, SoLuong ,TenTheLoai, idSach from Sach, TacGia, TheLoai  where Sach.DaXoa=0 and Sach.idTacGia = TacGia.idTacGia and Sach.idTheLoai = TheLoai.idTheLoai"
        'B4: Thuc thi chuoi strSQL
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    '  ' chỉ cho gia tri xoa = 1 van con trong CSDL 

    Public Function LaySachTheoTheLoaiVaTacGia(ByVal s As SachDTO) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select * from Sach where idTheLoai = ? and idTacGia=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idTheLoai", OleDbType.Integer)
        cmd.Parameters("@idTheLoai").Value = s.LayIDTheLoai()
        cmd.Parameters.Add("@idTacgia", OleDbType.Integer)
        cmd.Parameters("@idTacgia").Value = s.layTacGia()
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        cn.Close()
        a.Fill(kq)
        Return kq
    End Function

    Public Function timtheoten(ByVal s As String) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "SELECT * FROM  Sach  where  TenSach LIKE  ? + '%'"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@tensach", OleDbType.WChar)
        cmd.Parameters("@tensach").Value = s.Trim
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        cn.Close()
        a.Fill(kq)
        Return kq
    End Function

    Public Function timtheotacgia(ByVal s As SachDTO) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select * from Sach  where idTacgia = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idTacGia", OleDbType.Integer)
        cmd.Parameters("@idTacGia").Value = s.layTacGia()
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        cn.Close()
        a.Fill(kq)
        Return kq
    End Function

    Public Function ThongKeTon(ByVal s As SachDTO, ByVal ct As ChiTietSachDTO, ByRef thang As Integer) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select s.NgayNhap,s.TenSach,ct.TonDau,ct.PhatSinh,ct.TonCuoi from Sach s, ChiTietSach ct where s.idSach = ct.idSach and Month(s.NgayNhap)=? "
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@thang", OleDbType.Integer)
        cmd.Parameters("@thang").Value = thang
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        cn.Close()
        a.Fill(kq)
        Return kq
    End Function

    Public Function tim(ByVal s As String) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "SELECT * FROM  Sach WHERE TenSach LIKE  ? + '%'"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@tensach", OleDbType.WChar)
        cmd.Parameters("@tensach").Value = s
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        cn.Close()
        a.Fill(kq)
        Return kq
    End Function

    Public Function LaySachTheoTheLoai(ByVal s As SachDTO) As DataTable
        Dim kq As New DataTable

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select * from Sach where idTheLoai = ? and DaXoa = 0 "
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idTheLoai", OleDbType.Integer)
        cmd.Parameters("@idTheLoai").Value = s.LayIDTheLoai()
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        cn.Close()
        a.Fill(kq)
        Return kq
    End Function

    Public Function LayGia(ByVal id As SachDTO) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select Gia from Sach where idSach = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)
        cmd.Parameters("@idSach").Value = id.LayIdSach()
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Function laySoLuong(ByVal id As SachDTO) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select SoLuong from Sach where idSach = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)

        cmd.Parameters("@idSach").Value = id.LayIdSach()
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Function KiemTaSoLuong() As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select SoLuong from Sach where DaXoa = 0"
        'B4: Thuc thi chuoi strSQL
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    ' dung để xóa thể loại
    Public Sub XoaTheLoai(ByVal idtheloai As TheLoaiDTO)
        Dim cn As OleDbConnection
        cn = DatabaseProvider.ConnectionData
        Dim sql As String = "delete  from Sach where idTheLoai = ? "   '  ' ' ' ''" & idTheLoai.LayIdTheLoai & "'
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idTheLoai", OleDbType.Integer)
        cmd.Parameters("@idTheLoai").Value = idTheLoai.LayIdTheLoai
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub


    Public Sub CapNhatSoLuong(ByVal book As SachDTO)

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "Update Sach set SoLuong = ? where idSach = ? "
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@SoLuong", OleDbType.Integer)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)

        cmd.Parameters("@SoLuong").Value = book.LaySoLuong
        cmd.Parameters("@idSach").Value = book.LayIdSach

        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Sub ThemDauSachDaTonTai(ByVal book As SachDTO, ByVal sl As Integer)

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "Update Sach set NgayNhap=? , SoLuong = ? where idSach = ? "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
        cmd.Parameters.Add("@SoLuong", OleDbType.Integer)
        cmd.Parameters.Add("@idSach", OleDbType.Integer)

        cmd.Parameters("@NgayNhap").Value = book.LayNgayNhap
        cmd.Parameters("@SoLuong").Value = sl
        cmd.Parameters("@idSach").Value = book.LayIdSach

        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub


End Class
